/*
* 工装夹具管理模块
*/
const express = require('express');
const router = express.Router();
// 引入数据库
const db = require("../../../config/db");
// 引入时间包
const moment = require("moment");
// 引入方法库
const { toCamelCase, toUnderlineCase, verifyToken} = require("../../../config/utils");
// 引入日志
const log = require("../../log");

// 获取所有工装夹具列表
router.get("/list", async (req, res) => {
    console.log("===========获取工装夹具列表接口============");
    // 获取参数
    let { pageNum, pageSize, toolCode, toolName, brand, spec, status } = req.query;
    // 拼接sql语句
    let sql = "select * from tm_tool";
    let sql_where = ` where 1=1`;
    if(toolCode){
        sql_where += ` and tool_code like '%${toolCode}%'`;
    }
    if(toolName){
        sql_where += ` and tool_name like '%${toolName}%'`;
    }
    if(brand){
        sql_where += ` and brand like '%${brand}%'`;
    }
    if(spec){
        sql_where += ` and spec like '%${spec}%'`;
    }
    if(status){
        sql_where += ` and status = '${status}'`;
    }
    if(toolCode || toolName || brand || spec || status){
        sql += sql_where;
    }
    
    // 分页
    sql += ` limit ${(pageNum-1)*pageSize}, ${pageSize}`;
    // 查询数据库
    db.query("查询工装夹具列表", sql, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询工装夹具列表失败", data: err});
            return;
        }
        // 查询总数
        db.query("查询工装夹具总数", `select count(*) as count from tm_tool ${sql_where}`, (err, count)=>{
            if(err){
                res.send({code: 500, msg: "查询工装夹具总数失败", data: err});
                return;
            }
            if(result.length > 0){
                 // 变量Json数组key转成下划线第一个大写
                 result = result.map(item => {
                    return toCamelCase(item)
                })
                res.send({code: 200, msg: "查询工装夹具列表成功", rows: result, total: count[0].count});
            }else{
                res.send({code: 200, msg: "查询工装夹具列表成功", rows: [], total: 0});
            }
        })
    })
})


// 新增工装夹具
router.post("/", (req, res)=>{
    console.log("===========新增工装夹具接口============");
    // 获取参数
    let { toolTypeId, toolName, quantity, codeFlag, remark, brand, spec, status, toolCode, quantityAvail, maintenType, nextMaintenDate, nextMaintenPeriod } = req.body;
    // 获取创建人
    let {username} = verifyToken(req.headers.authorization);
    // 获取创建时间
    let createTime = moment().format("YYYY-MM-DD HH:mm:ss");
    // 判断工装夹具类型和工装夹具名称是否重复
    db.query("查询工装夹具类型和工装夹具名称是否重复", `select * from tm_tool where tool_type_id = '${toolTypeId}' or tool_name = '${toolName}'`, (err, sql_check_result)=>{
        if(err){
            res.send({code: 500, msg: "查询工装夹具类型和工装夹具名称是否重复失败", data: err});
            return;
        }
        if(sql_check_result.length > 0){
            res.send({code: 500, msg: "工装夹具类型或工装夹具名称已存在"});
            return;
        }
        // 根据工装夹具类型id查询工装夹具类型名称
        db.query("查询工装夹具类型名称", `select tool_type_name from tm_tool_type where tool_type_id = '${toolTypeId}'`, (err, tool_type_name_result)=>{
            if(err){
                res.send({code: 500, msg: "查询工装夹具类型名称失败", data: err});
                return;
            }
            if(tool_type_name_result.length > 0){
                // 插入数据库
                let sql = `insert into tm_tool ( tool_type_id, tool_name, quantity, code_flag, remark, brand, spec, status, tool_code, quantity_avail, mainten_type, 
                next_mainten_date, next_mainten_period, create_by, create_time, tool_type_name)
                values 
                ( '${toolTypeId}', '${toolName}', '${quantity}', '${codeFlag}', ${remark ? "'"+remark+"'" : null}, ${brand ? "'"+brand+"'" : null},
                ${spec ? "'"+spec+"'" : null}, ${status ? "'"+status+"'" : null}, ${toolCode ? "'"+toolCode+"'" : null}, ${quantityAvail ? "'"+quantityAvail+"'" : null}, 
                ${maintenType ? "'"+maintenType+"'" : null}, ${nextMaintenDate ? "'"+nextMaintenDate+"'" : null}, ${nextMaintenPeriod ? "'"+nextMaintenPeriod+"'" : null},
                '${username}', '${createTime}', '${tool_type_name_result[0].tool_type_name}')`;
                db.query("新增工装夹具", sql, (err, result)=>{
                    if(err){
                        res.send({code: 500, msg: "新增工装夹具失败", data: err});
                        return;
                    }
                    if(result.affectedRows > 0){
                        res.send({code: 200, msg: "新增工装夹具成功"});
                    }else{
                        res.send({code: 500, msg: "新增工装夹具失败"});
                    }
                })
            }
        })
        
    })
})

// 查询单个工装夹具
router.get("/:id", (req, res)=>{
    console.log("===========查询单个工装夹具接口============");
    // 获取参数
    let id = req.params.id;
    db.query("查询单个工装夹具", `select * from tm_tool where tool_id = ${id}`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询单个工装夹具失败", data: err});
            return;
        }
        if(result.length > 0){
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
                return toCamelCase(item)
            })
            res.send({code: 200, msg: "查询单个工装夹具成功", data: result[0]});
        }else{
            res.send({code: 500, msg: "查询单个工装夹具失败"});
        }
    })
})

// 修改工装夹具
router.put("/", (req, res)=>{
    console.log("===========修改工装夹具接口============");
    // 获取参数
    let {toolId, toolTypeId, toolName, quantity, codeFlag, remark, brand, spec, status, toolCode, quantityAvail, maintenType, nextMaintenDate, nextMaintenPeriod } = req.body;
    // 获取修改人
    let {username} = verifyToken(req.headers.authorization);
    // 获取修改时间
    let updateTime = moment().format("YYYY-MM-DD HH:mm:ss");
    // 判断保养类型
    if(maintenType == "REGULAR"){
        nextMaintenPeriod = null
    }
    if(maintenType == "USAGE"){
        nextMaintenDate = null
    }
    // 判断工装夹具类型和工装夹具名称是否重复
    db.query("查询工装夹具类型和工装夹具名称是否重复", `select * from tm_tool where (tool_type_id = '${toolTypeId}' or tool_name = '${toolName}') and tool_id != '${toolId}'`, (err, sql_check_result)=>{
        if(err){
            res.send({code: 500, msg: "查询工装夹具类型和工装夹具名称是否重复失败", data: err});
            return;
        }
        if(sql_check_result.length > 0){
            res.send({code: 500, msg: "工装夹具类型或工装夹具名称已存在"});
            return;
        }
        // 根据工装夹具类型id查询工装夹具类型名称
        db.query("查询工装夹具类型名称", `select tool_type_name from tm_tool_type where tool_type_id = '${toolTypeId}'`, (err, tool_type_name_result)=>{
            if(err){
                res.send({code: 500, msg: "查询工装夹具类型名称失败", data: err});
                return;
            }
            if(tool_type_name_result.length > 0){
                // 插入数据库
                let sql = `update tm_tool set tool_type_id = '${toolTypeId}', tool_name = '${toolName}', quantity = ${quantity}, code_flag = '${codeFlag}',
                remark = ${remark ? "'"+remark+"'" : null}, brand = ${brand ? "'"+brand+"'" : null}, spec = ${spec ? "'"+spec+"'" : null}, 
                status = ${status ? "'"+status+"'" : null}, tool_code = ${toolCode ? "'"+toolCode+"'" : null}, quantity_avail = ${quantityAvail ? "'"+quantityAvail+"'" : null}, 
                mainten_type = ${maintenType ? "'"+maintenType+"'" : null}, next_mainten_date = ${nextMaintenDate ? "'"+nextMaintenDate+"'" : null}, 
                next_mainten_period = ${nextMaintenPeriod ? "'"+nextMaintenPeriod+"'" : null}, update_by = '${username}', update_time = '${updateTime}'
                where tool_id = ${toolId}`;
                db.query("修改工装夹具", sql, (err, result)=>{
                    if(err){
                        res.send({code: 500, msg: "修改工装夹具失败", data: err});
                        return;
                    }
                    if(result.affectedRows > 0){
                        res.send({code: 200, msg: "修改工装夹具成功"});
                    }else{
                        res.send({code: 500, msg: "修改工装夹具失败"});
                    }
                })
            }
        })
        
    })
})

// 删除工装夹具
router.delete("/:id", (req, res) => {
    console.log("===========删除工装夹具接口============");
    // 获取参数
    let {id} = req.params;
     // 拼接sql,删除多条数据
     let sql = `delete from tm_tool where tool_id in(${id})`;
    // 查询数据库
    db.query("删除工装夹具", sql, (err, result) => {
        if (err) {
            res.send({code: 500, msg: "删除工装夹具失败", data: err});
            return
        }
        if(result.affectedRows > 0){
            res.send({code: 200, msg: "删除工装夹具成功"})
        }else{
            res.send({code: 500, msg: "删除工装夹具失败"})
        }
    })
})


module.exports = router;
